<?php
defined('SYSPATH') OR die('No direct access allowed.');
/**
 * report.php
 * @author ranuandra
 *
 * 8 Sep 2011 16:32:37
 *
 * 
 */

class Controller_Report extends Kohana_Mapi
{
	public function action_stock()
	{
		$material = ORM::factory('material')->find_all();
		
	}
	
	public function action_daily()
	{
		$this->auto_render = false;
		
		$bulan = date("m");
		$tahun = date("Y");
		$datas = ORM::factory('stock')->where('bulan', '=', $bulan)->where('tahun', '=', $tahun)->find_all();
		
		foreach($datas as $data)
		{
			if($data->jumlah > $data->material->buffer->jumlah)
			{
				$remark = "Safe";
			}
			elseif($data->jumlah > $data->material->buffer->jumlah)
			{
				$remark = "Prepare";
			}
			else
			{
				$remark = "Critical";
			}
			$stocks = array(
				'location_name' => $data->location->name,
				'code'			=> $data->material->code,
				'material_name' => $data->material->name,
				'last_stock'	=> $data->jumlah,
				'buffer'		=> $data->material->buffer->jumlah,	
				'remark'		=> $remark,			
			);
			$hasil[] = $stocks;
		}
		
		echo json_encode(array("success" => true, "message" => "Loaded data", "dailyreport" => $hasil));
	}
	
	public function action_dailyxls()
	{
		//$this->auto_render = false;
		
		$bulan = date("m");
		$tahun = date("Y");
		$datas = ORM::factory('stock')->where('bulan', '=', $bulan)->where('tahun', '=', $tahun)->find_all();
		
		$hasil[] = array("Location", "Code", "Material Name", "Last Stock", "Buffer", "Remark");
		foreach($datas as $data)
		{
			if($data->jumlah > $data->material->buffer->jumlah)
			{
				$remark = "Safe";
			}
			elseif($data->jumlah > $data->material->buffer->jumlah)
			{
				$remark = "Prepare";
			}
			else
			{
				$remark = "Critical";
			}
			$stocks = array(
				$data->location->name, $data->material->code, $data->material->name, $data->jumlah, $data->material->buffer->jumlah, $remark			
			);
			$hasil[] = $stocks;
		}

		
		$ws = new Spreadsheet(array(
		'author'       => 'MSCP',
		'title'        => 'Report',
		'subject'      => 'Subject',
		'description'  => 'Description',
		));
		
		$ws->set_active_sheet(0);
		$as = $ws->get_active_sheet();
		$as->setTitle('Report');
		
		$as->getDefaultStyle()->getFont()->setSize(9);
		
		$as->getColumnDimension('A')->setWidth(7);
		$as->getColumnDimension('B')->setWidth(40);
		$as->getColumnDimension('C')->setWidth(12);
		$as->getColumnDimension('D')->setWidth(10);
		$as->getColumnDimension('E')->setWidth(10);
		$as->getColumnDimension('F')->setWidth(10);
		
		//print_r($hasil);
		$ws->set_data($hasil, false);
		$ws->send(array('name'=>'report', 'format'=>'Excel5'));
	}
	
	public function action_detail()
	{
		$this->auto_render = false;
		
		//$bulan = date("m");
		//$tahun = date("Y");
		//$datas = ORM::factory('stock')->where('bulan', '=', $bulan)->where('tahun', '=', $tahun)->find_all();
		$datains = ORM::factory('detailmaterialin')->find_all();
		$dataouts = ORM::factory('detailmaterialout')->find_all();
		
		$hasil = array();
		foreach($datains as $datain)
		{
			$datas= array(
				'date' 			=> $datain->materialin->tgl,
				'code'			=> $datain->material->code,
				'material_name' => $datain->material->name,
				'transaction'	=> 'IN',
				'qty'			=> $datain->jumlah,	
				'no_order'		=> $datain->materialin->no_order,	
				'origin'		=> '',
				'truck_number'	=> $datain->materialin->truck_number,	
				'location'		=> $datain->location->name	
			);
			$hasil[] = $datas;
		}
		
		foreach($dataouts as $dataout)
		{
			$datas= array(
				'date' 			=> $dataout->materialout->tgl,
				'code'			=> $dataout->material->code,
				'material_name' => $dataout->material->name,
				'transaction'	=> 'OUT',
				'qty'			=> $dataout->jumlah,	
				'no_order'		=> $dataout->materialout->no_order,	
				'origin'		=> '',
				'truck_number'	=> '',	
				'location'		=> $dataout->location->name
			);
			$hasil[] = $datas;
		}
		
		echo json_encode(array("success" => true, "message" => "Loaded data", "detailreport" => $hasil));
	}
	
	public function action_monthly()
	{
		$this->auto_render = false;
		
		$materials = ORM::factory('material')->find_all();

		$bulan = date("m");
		$tahun = date("Y");
		//$datas = ORM::factory('stock')->where('bulan', '=', $bulan)->where('tahun', '=', $tahun)->find_all();
		
		$hasil = array();
		$line = array();
		foreach($materials as $material)
		{
			$code = $material->code;
			$nama_material = $material->name;
			
			$line['id'] = $material->id;
			$line['code'] = $code;
			$line['material_name'] = $nama_material;
			$line['data'] = 'LAST STOCK';
			
			for($i=$bulan; $i>=1; $i--)
			{
				$ke = $i;
				$datas = ORM::factory('stock')->where('bulan', '=', $i)->where('tahun', '=', $tahun)->where('material_id', '=', $material->id)->find_all();
				//print_r($datas);
				if(!isset($datas->jumlah))
				{
					$jml = 0;
					$baca = array();
					while($ke>=1)
					{
						$stoks = ORM::factory('stock')->where('bulan', '=', $ke)->where('tahun', '=', $tahun)->where('material_id', '=', $material->id)->find_all();
						if(isset($stocks->jumlah))
						{
							$jml = $stocks->jumlah;
							$baca = $stoks->as_array();
						}
						$ke--;
						
					}
					$stock_awal = $jml;
				}
				else 
				{
					$stock_awal = $datas->jumlah;
				}
				$line[$i] = $stock_awal;
			}
			$hasil[] = $line;
			unset($line);
			$buffers = ORM::factory('material')->where('id', '=', $material->id)->find();
			
			$line['code'] = 'Buffer';
			$line['material_name'] = $buffers->buffer->jumlah;
			$line['data'] = 'IN';

			for($i=$bulan; $i>=1; $i--)
			{
				$materialin = DB::select(array(DB::expr('SUM(detailmaterialins.jumlah)'), 'total'))->from('detailmaterialins')
														  ->join('materialins')
														  ->on('materialins.id', '=', 'detailmaterialins.materialin_id')
														  ->where('detailmaterialins.material_id', '=', $material->id)
														  ->where(DB::expr('MONTH(materialins.tgl)'), '=', $i)
														  ->where(DB::expr('YEAR(materialins.tgl)'), '=', $tahun);
				$matin = $materialin->execute();
				
														  //print_r($materialin);
				$in = $matin['total'];
				$line[$i] = $in;
			}
			$hasil[] = $line;
			unset($line);
			
			$line['data'] = 'OUT';
			for($i=$bulan; $i>=1; $i--)
			{
				$materialout = DB::select(array(DB::expr('SUM(detailmaterialouts.jumlah)'), 'total'))->from('detailmaterialouts')
														  ->join('materialouts')
														  ->on('materialouts.id', '=', 'detailmaterialouts.materialout_id')
														  ->where('detailmaterialouts.material_id', '=', $material->id)
														  ->where(DB::expr('MONTH(materialouts.tgl)'), '=', $i)
														  ->where(DB::expr('YEAR(materialouts.tgl)'), '=', $tahun)
														  ->execute();
				if($datas->count() < 1)
				{
					$out = 0;
				}
				else 
				{
					$out = $materialout['total'];
				}
				$line[$i] = $out;
			}
			$hasil[] = $line;			
			
		}
		
		echo json_encode(array("success" => true, "message" => "Loaded data", "monthlyreport" => $hasil));
	}
	
}